﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataClass
{
    public class ASDataProvider
    {

        #region Constructor
        public ASDataProvider()
        {
            if (ASSqlServer.sqlConnection.State != ConnectionState.Open)
            {
                ASSqlServer asSqlServer = new ASSqlServer();
                asSqlServer.OpenConnection();
            }
        }
        #endregion

        //Tra loi tu SQL server gui len
        private string errorMessage = "";
        public string ErrorMessage
        {
            get { return errorMessage; }
        }

        //Gui lenh xuong SQL server de thuc hien Procedured
        private string commandText = "";
        public string CommandText
        {
            get { return commandText; }
            set { commandText = value; }
        }

        //Phan biet thuoc tinh CommandText là Procedured hay SQL query
        private CommandType commandType = CommandType.Text;
        public CommandType CommandType
        {
            set { commandType = value; }
            get { return commandType; }
        }

        //Them tham so vao Command doi voi Procedured va SQL query co tham so
        private void DefineSqlParameter(SqlCommand sqlCommand, string Parameter, object Value)
        {
            //Khoi tao doi tuong SqlParameter
            SqlParameter sqlParameter = new SqlParameter();
            sqlParameter.ParameterName = Parameter;
            sqlParameter.SqlValue = Value;
            sqlCommand.Parameters.Add(sqlParameter);
        }

        //Overload DefineSqlParameter de them Array tham so va gia tri tuong ung vao SqlCommand
        private void DefineSqlParameter(SqlCommand sqlCommand, string[] parameterName, object[] parameterValue)
        {
            //Khoi tao doi tuong SqlParameter
            SqlParameter sqlParameter;
            for (int i = 0; i < parameterName.Length; i++)
            {
                sqlParameter = new SqlParameter();
                sqlParameter.ParameterName = parameterName[i];
                sqlParameter.SqlValue = parameterValue[i];
                sqlCommand.Parameters.Add(sqlParameter);
            }

        }

        //Phuong thuc nhan Ten tham so & Gia tri tuong ung de truyen vao Procedured
        public int ExecuteNonQuery(string parameterName, String parameterValue)
        {
            int effectedRecord = 0;
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);

                //Khai boa goi phuong thuc ExecuteNonQuery
                effectedRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return effectedRecord;
        }

        //Overload phuong thuc ExecuteNonQuery nhan Array tham so va Array gia tri
        public int ExecuteNonQuery(string[] parameterName, object[] parameterValue)
        {
            int effectedRecord = 0;
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);

                //Khai boa goi phuong thuc ExecuteNonQuery
                effectedRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return effectedRecord;
        }

        //Overload phuong thuc ExecuteNonQuery khong tham so
        public int ExecuteNonQuery()
        {
            int effectedRecord = 0;
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;
                //Khai boa goi phuong thuc ExecuteNonQuery
                effectedRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return effectedRecord;
        }

        //Overload phuong thuc ExecuteNonQuery co gia tri tra ve
        public int ExecuteNonQuery(string[] parameterName, object[] parameterValue, string returnParameterName)
        {
            int effectedRecord = 0;
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;
                SqlParameter sqlParameter;

                for (int i = 0; i < parameterName.Length; i++)
                {
                    //Khai bao thuoc tinh Direction de chi dinh tham so ung voi gia tri tra ve
                    if (parameterName[i] == returnParameterName)
                    {
                        //Khai bao va khoi tao doi tuong sqlParameter
                        sqlParameter = new SqlParameter(parameterName[i], SqlDbType.Int);
                        sqlParameter.Direction = ParameterDirection.ReturnValue;
                    }
                    else
                    {
                        //Khai bao va khoi tao doi tuong sqlParameter
                        sqlParameter = new SqlParameter();
                        sqlParameter.ParameterName = parameterName[i];
                        sqlParameter.SqlValue = parameterValue[i];
                    }
                    sqlCommand.Parameters.Add(sqlParameter);
                }

                //Khai bao goi phuong thuc ExecuteNonQuery
                sqlCommand.ExecuteNonQuery();

                //Khai bao lay gia tri tra ve
                effectedRecord = (int)sqlCommand.Parameters[returnParameterName].Value;
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return effectedRecord;
        }

        //Lay gia tri tra ve
        public string GetReturnString(string[] parameterName, object[] parameterValue, string returnParameterName)
        {
            string str = null;
            //int effectedRecord = 0;
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;
                SqlParameter sqlParameter;

                for (int i = 0; i < parameterName.Length; i++)
                {
                    //Khai bao thuoc tinh Direction de chi dinh tham so ung voi gia tri tra ve
                    if (parameterName[i] == returnParameterName)
                    {
                        //Khai bao va khoi tao doi tuong sqlParameter
                        //sqlParameter = new SqlParameter();
                        //sqlParameter.ParameterName = parameterName[i];
                        //sqlParameter.Direction = ParameterDirection.Output;

                        sqlCommand.Parameters.Add(returnParameterName, SqlDbType.NVarChar,100).Direction = ParameterDirection.Output;

                    }
                    else
                    {
                        //Khai bao va khoi tao doi tuong sqlParameter
                        sqlParameter = new SqlParameter();
                        sqlParameter.ParameterName = parameterName[i];
                        sqlParameter.SqlValue = parameterValue[i];

                        sqlCommand.Parameters.Add(sqlParameter);
                    }
                    
                }

                //Khai bao goi phuong thuc ExecuteNonQuery
                sqlCommand.ExecuteNonQuery();

                str = sqlCommand.Parameters[returnParameterName].Value.ToString();
                //Khai bao lay gia tri tra ve
                //effectedRecord = (int)sqlCommand.Parameters[returnParameterName].Value;
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }
            return str;
            // effectedRecord;
        }

        //End of lay gia tri tra ve
        

        //Lay gia tri tu phat bieu SQL hay Procedured
        public object ExecuteScalar()
        {
            object objValue = null;

            //Khai bao va tao doi tuong SqlCommand
            SqlCommand sqlCommand = new SqlCommand();

            //Khai bao thuoc tinh cho doi tuong sqlCommand
            sqlCommand.CommandText = commandText;
            sqlCommand.Connection = ASSqlServer.sqlConnection;
            sqlCommand.CommandType = commandType;
            try
            {
                //Khai bao goi phuong thuc ExecuteScalar
                objValue = sqlCommand.ExecuteScalar();

            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }
            return objValue;
        }

        //Lay gia tri tu phat bieu SQL hay Procedured co tham so
        public object ExecuteScalar(string parameterName, object parameterValue)
        {
            object objValue = null;

            //Khai bao va tao doi tuong SqlCommand
            SqlCommand sqlCommand = new SqlCommand();

            //Khai bao thuoc tinh cho doi tuong sqlCommand
            sqlCommand.CommandText = commandText;
            sqlCommand.Connection = ASSqlServer.sqlConnection;
            sqlCommand.CommandType = commandType;

            //khai bao phuong thuc DefineSqlParameter
            DefineSqlParameter(sqlCommand, parameterName, parameterValue);
            try
            {
                //Khai bao goi phuong thuc ExecuteScalar
                objValue = sqlCommand.ExecuteScalar();

            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }
            return objValue;
        }

        //Lay gia tri tu phat bieu SQL hay Procedured co nhieu tham so
        public object ExecuteScalar(string[] parameterName, object[] parameterValue)
        {
            object objValue = null;

            //Khai bao va tao doi tuong SqlCommand
            SqlCommand sqlCommand = new SqlCommand();

            //Khai bao thuoc tinh cho doi tuong sqlCommand
            sqlCommand.CommandText = commandText;
            sqlCommand.Connection = ASSqlServer.sqlConnection;
            sqlCommand.CommandType = commandType;

            //khai bao phuong thuc DefineSqlParameter
            DefineSqlParameter(sqlCommand, parameterName, parameterValue);
            try
            {
                //Khai bao goi phuong thuc ExecuteScalar
                objValue = sqlCommand.ExecuteScalar();

            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }
            return objValue;
        }

        //Lay ra Array Object ung voi List Data Column in Table
        public object[] GetObjects(string parameterName, string parameterValue)
        {
            object[] objValue = null;
            try
            {
                //Khai bao khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khai bao thuoc tinh cho doi tuong SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;
                int i = 0;

                //Khai bao goi phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);
                SqlDataReader sqlDataReader;

                //Khai bao goi phuong thuc ExecuteReader
                sqlDataReader = sqlCommand.ExecuteReader();

                //Khai bao lay cot du lieu
                i = sqlDataReader.FieldCount;
                if (sqlDataReader.Read())
                {
                    //Khai bao va khoi tao doi tuong object
                    objValue = new object[i];

                    //Khai bao goi phuong thuc GetValues
                    sqlDataReader.GetValues(objValue);
                }

                sqlDataReader.Close();
                sqlDataReader.Dispose();

            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return objValue;
        }

        //Lay du lieu cua bang hay view tu CSDL va giu trong Object DataTable ( ko tham so )
        public DataTable GetDataTable()
        {
            //Khai bao khoi tao doi tuong DataTable
            DataTable dataTable = new DataTable();
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khai bao thuoc tinh cho SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao va khoi tao doi tuong SqlDataAdapter
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                //Khai bao goi phuong thuc Fill
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return dataTable;
        }

        //Lay du lieu cua bang hay view tu CSDL va giu trong Object DataTable ( co tham so )
        public DataTable GetDataTable(string parameterName, string parameterValue)
        {
            //Khai bao khoi tao doi tuong DataTable
            DataTable dataTable = new DataTable();
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khia bao thuoc tinh cho SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao goi phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);

                //Khai bao va khoi tao doi tuong SqlDataAdapter
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                //Khai bao goi phuong thuc Fill
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return dataTable;
        }

        //Lay du lieu cua bang hay view tu CSDL va giu trong Object DataTable ( nhieu tham so )
        public DataTable GetDataTable(string[] parameterName, object[] parameterValue)
        {
            //Khai bao khoi tao doi tuong DataTable
            DataTable dataTable = new DataTable();
            try
            {
                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khia bao thuoc tinh cho SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao goi phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);

                //Khai bao va khoi tao doi tuong SqlDataAdapter
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                //Khai bao goi phuong thuc Fill
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return dataTable;
        }

        //Lay du lieu cua bang hay view tu CSDL va giu trong Object DataRow ( ko tham so )
        public DataRow GetDataRow()
        {
            //Khai bao khoi tao doi tuong DataTable
            DataRow dataRow = null;
            try
            {
                //Khai bao va khoi tao doi tuong DataTable
                DataTable dataTable = new DataTable();

                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khia bao thuoc tinh cho SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao va khoi tao doi tuong SqlDataAdapter
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                //Khai bao goi phuong thuc Fill
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();

                //Neu ton tai mau tin trong du lieu
                if (dataTable.Rows.Count > 0)
                {
                    //Lay Row dau tien
                    dataRow = dataTable.Rows[0];
                }
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return dataRow;
        }

        //Lay du lieu cua bang hay view tu CSDL va giu trong Object DataRow ( co 1 tham so )
        public DataRow GetDataRow(string parameterName, string parameterValue)
        {
            //Khai bao khoi tao doi tuong DataTable
            DataRow dataRow = null;
            try
            {
                //Khai bao va khoi tao doi tuong DataTable
                DataTable dataTable = new DataTable();

                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khia bao thuoc tinh cho SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao goi phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);

                //Khai bao va khoi tao doi tuong SqlDataAdapter
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                //Khai bao goi phuong thuc Fill
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();

                //Neu ton tai mau tin trong du lieu
                if (dataTable.Rows.Count > 0)
                {
                    //Lay Row dau tien
                    dataRow = dataTable.Rows[0];
                }
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return dataRow;
        }

        //Lay du lieu cua bang hay view tu CSDL va giu trong Object DataRow ( nhieu tham so )
        public DataRow GetDataRow(string[] parameterName, object[] parameterValue)
        {
            //Khai bao khoi tao doi tuong DataTable
            DataRow dataRow = null;
            try
            {
                //Khai bao va khoi tao doi tuong DataTable
                DataTable dataTable = new DataTable();

                //Khai bao va khoi tao doi tuong SqlCommand
                SqlCommand sqlCommand = new SqlCommand();

                //Khia bao thuoc tinh cho SqlCommand
                sqlCommand.CommandText = commandText;
                sqlCommand.Connection = ASSqlServer.sqlConnection;
                sqlCommand.CommandType = commandType;

                //Khai bao goi phuong thuc DefineSqlParameter
                DefineSqlParameter(sqlCommand, parameterName, parameterValue);

                //Khai bao va khoi tao doi tuong SqlDataAdapter
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                //Khai bao goi phuong thuc Fill
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();

                //Neu ton tai mau tin trong du lieu
                if (dataTable.Rows.Count > 0)
                {
                    //Lay Row dau tien
                    dataRow = dataTable.Rows[0];

                }
            }
            catch (Exception ex)
            {
                errorMessage = "Error: " + ex.Message;
            }

            return dataRow;
        }
    }


}
